package com.starbaba.stat.service.impl;

import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Component;

import com.base.common.database.BaseDao;
import com.starbaba.core.web.page.PageResponse;
import com.starbaba.stat.model.StatActivitySummaryModel;
import com.starbaba.stat.service.StatActivitySummaryService;
import com.starbaba.stat.utils.Constant;


@SuppressWarnings("rawtypes")
@Component
public class StatActivityServiceSummaryImpl implements StatActivitySummaryService {

	static BaseDao dao = new BaseDao(Constant.LOAN_STAT_DB_POOL);// Constant.LOAN_STAT_DB_POOL
	
	@Override
	public void showPage(Map<String, Object> paramsMap) throws Exception {
		List<StatActivitySummaryModel> prdList = dao.list("select a.prd_id, b.name prd_name from (select prd_id from stat.stat_activity_summary group by prd_id order by null )a,loan.comm_product_prdid b where a.prd_id = b.prdid ", StatActivitySummaryModel.class);
		List<StatActivitySummaryModel> channelList = dao.list("select a.channel, b.channel_name from (select channel from stat.stat_activity_summary group by channel order by null )a,loan.comm_product_channel b where a.channel = b.channel_code ", StatActivitySummaryModel.class);
		List<StatActivitySummaryModel> activityList = dao.list("select activity_id, activity_name from stat_activity_summary where activity_id is not null GROUP BY activity_id order by null ", StatActivitySummaryModel.class);
		StatActivitySummaryModel total = new StatActivitySummaryModel();
//		total.setPrd_id("total");
//		total.setChannel("total");
		total.setActivity_id("total");
//		total.setPrd_name("全部");
//		total.setChannel_name("全部");
		total.setActivity_name("全部");		
//		prdList.add(0, total);
//		channelList.add(0, total);
		activityList.add(0, total);
		paramsMap.put("prdList", prdList);
		paramsMap.put("channelList", channelList);
		paramsMap.put("activityList", activityList);
	}

	@Override
	public List getList(Integer start, Integer length, PageResponse pageResponse, Map<String, Object> paramsMap) throws Exception {
		
		String startTime = (String) paramsMap.get("startTime");
		String endTime = (String) paramsMap.get("endTime");
		String platform = (String) paramsMap.get("platform");
		String prd_id = (String) paramsMap.get("prd_id");
		String channel = (String) paramsMap.get("channel");
		String activity_id = (String) paramsMap.get("activity");
		String user_type = (String) paramsMap.get("user_type");
		
		StringBuilder whereParams = new StringBuilder("where 1 = 1 ");
		if (StringUtils.isNotEmpty(startTime)) {
			whereParams.append("and time >= '" + startTime + "' ");
		}
		if (StringUtils.isNotEmpty(endTime)) {
			whereParams.append("and time <= '" + endTime + "' ");
		}
		if (StringUtils.isNotEmpty(platform) && "total".equals(platform)) {
			whereParams.append("and platform = 'total' ");
		} else if (StringUtils.isNotEmpty(platform)) {
			whereParams.append("and platform = '" + platform + "' and platform <> 'total' ");
		} else {
			whereParams.append("and platform <> 'total' ");
		}
		if (StringUtils.isNotEmpty(prd_id)) {
			whereParams.append("and prd_id = '" + prd_id + "' ");
		}
		if (StringUtils.isNotEmpty(channel)) {
			whereParams.append("and channel = '" + channel + "' ");
		}
		if (StringUtils.isNotEmpty(activity_id) && "total".equals(activity_id)) {
			whereParams.append("and activity_id = 'total' ");
		} else if (StringUtils.isNotEmpty(activity_id)) {
			whereParams.append("and activity_id = '" + activity_id + "' and activity_id <> 'total' ");
		} else {
			whereParams.append("and activity_id <> 'total' ");
		}
		if (StringUtils.isNotEmpty(user_type) && "total".equals(user_type)) {
			whereParams.append("and user_type = 'total' ");
		} else if (StringUtils.isNotEmpty(user_type)) {
			whereParams.append("and user_type = '" + user_type + "' and user_type <> 'total' ");
		} else {
			whereParams.append("and user_type <> 'total' ");
		}
		String groupByString = "group by time, platform, prd_id, channel, activity_id, user_type ";
		StringBuilder sqlList = new StringBuilder();
		sqlList.append("select time, platform, prd_id, cpp.name prd_name, channel, cpc.channel_name channel_name, user_type, activity_id, activity_name, ");
		sqlList.append("UV, PV, clickUV, clickPV, pop_uv, pop_pv, login_uv, login_pv, ");
		sqlList.append("apply_loan_count, agree_loan_count, first_apply_loan_count, first_agree_loan_count, apply_loan_money, apply_earn, all_conversion_rate, ");
		sqlList.append("particion_conversion_rate, loan_conversion_rate FROM stat_activity_summary a ");
		sqlList.append("left join loan.comm_product_prdid cpp on a.prd_id = cpp.prdid ");
		sqlList.append("left join loan.comm_product_channel cpc on a.channel = cpc.channel_code ");
		sqlList.append(whereParams).append(groupByString);
		
		String sqlCount = "select count(1) from (" + sqlList.toString() + ") b ";
		if (start != null && length != null) {
			sqlList.append("order by time desc limit " + start + ", " + length);
		} else {
			sqlList.append("order by time desc");
		}
		
		int count = dao.getInt(sqlCount);
		List<StatActivitySummaryModel> list = dao.list(sqlList.toString(), StatActivitySummaryModel.class);
		for (StatActivitySummaryModel model : list) {
			if (StringUtils.isEmpty(model.getPlatform())) {
				model.setPlatform("未知");
			}
			if (StringUtils.isEmpty(model.getPrd_name())) {
				model.setPrd_name(model.getPrd_id());
			}
			if (StringUtils.isEmpty(model.getChannel_name())) {
				model.setChannel_name(model.getChannel());
			}
			if (StringUtils.isEmpty(model.getActivity_name())) {
				model.setActivity_name(model.getActivity_id());
			}
			if ("total".equals(platform)) {
				model.setPlatform("全部");
			}
			if ("total".equals(prd_id)) {
				model.setPrd_name("全部");
			}
			if ("total".equals(channel)) {
				model.setChannel_name("全部");
			}
			if ("total".equals(activity_id)) {
				model.setActivity_name("全部");
			}
			if ("total".equals(model.getUser_type())) {
				model.setUser_type("全部");
			} else if ("new".equals(model.getUser_type())) {
				model.setUser_type("新用户");
			} else if ("old".equals(model.getUser_type())) {
				model.setUser_type("老用户");
			}
		}
		pageResponse.setITotalRecords(count);
		pageResponse.setITotalDisplayRecords(count);
		return list;
	}
	
	@Override
	public  String getExportString(List<StatActivitySummaryModel> list) {
		StringBuilder builder = new StringBuilder();
		builder.append("日期,平台,产品,渠道,用户类型,活动id,活动名,活动UV,活动PV,参与按钮UV,参与按钮PV,"
				+ "登陆弹窗UV,登陆弹窗PV,登陆按钮UV,登陆按钮PV,"
				+ "申请时间借款人数,批款时间借款人数,申请时间借款人数(首借),批款时间借款人数(首借),申请时间借款人数,"
				+ "申请时间结算总收入,总转化率(UV),参与转化率(UV),借款转化率(UV)\r\n");
		for (StatActivitySummaryModel vo : list) {
			builder.append(vo.getTime()).append(","); 
			builder.append(vo.getPlatform()).append(","); 
			builder.append(vo.getPrd_name()).append(","); 
			builder.append(vo.getChannel_name()).append(",");
			if ("new".equals(vo.getUser_type())) {
				vo.setUser_type("新用户");
			} else if ("old".equals(vo.getUser_type())) {
				vo.setUser_type("老用户");
			} else {
				vo.setUser_type("全部");
			}
			builder.append(vo.getUser_type()).append(","); 
			builder.append(vo.getActivity_id()).append(","); 
			builder.append(vo.getActivity_name()).append(","); 
			builder.append(vo.getUV()).append(","); 
			builder.append(vo.getPV()).append(","); 
			builder.append(vo.getClickUV()).append(","); 
			builder.append(vo.getClickPV()).append(",");
			builder.append(vo.getPop_uv()).append(",");
			builder.append(vo.getPop_pv()).append(",");
			builder.append(vo.getLogin_uv()).append(",");
			builder.append(vo.getLogin_pv()).append(",");
			builder.append(vo.getApply_loan_count()).append(","); 
			builder.append(vo.getAgree_loan_count()).append(","); 
			builder.append(vo.getFirst_apply_loan_count()).append(","); 
			builder.append(vo.getFirst_agree_loan_count()).append(","); 
			builder.append(vo.getApply_loan_money()).append(","); 
			builder.append(vo.getApply_earn()).append(","); 
			builder.append(vo.getAll_conversion_rate()).append(","); 
			builder.append(vo.getParticion_conversion_rate()).append(","); 
			builder.append(vo.getLoan_conversion_rate()).append(","); 
		
			
			builder.append("\r\n");
		}
		return builder.toString();
	}
	
}